package com.xgpro.common.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.util.Map;
/**
 * @author tx
 * @version 1.0
 * @date 2019/10/18 21:44
 */
public class ExcelExporeUtil {


    /**
     * 将模板表格导入程序
     * @param map
     * @return
     * @throws Exception
     */
    public static XSSFWorkbook createWorkBook(Map<String, String> map)
            throws Exception
    {

        InputStream in = new FileInputStream(new File(map.get("temUrl")));
        
        XSSFWorkbook work = new XSSFWorkbook(in);
        // 得到excel的第0张表
        XSSFSheet sheet = work.getSheetAt(0);


        replaceExcelData(sheet, map,work);

        return work;
    }

    /**
     * 替换数据
     * @param sheet
     * @param map
     * @param workbook
     */
    public static void replaceExcelData(XSSFSheet sheet, Map<String, String> map ,XSSFWorkbook workbook)
    {
        int rowNum = sheet.getLastRowNum();
        for (int i = 0; i <= rowNum; i++)
        {//行
            XSSFRow row = sheet.getRow(i);
            /*if(i==0){//表头单独设置
                XSSFCell cell =row.getCell(0);
                cell.setCellValue(map.get("head"));//设置单元格内容
                XSSFCellStyle cellStyle = workbook.createCellStyle();//设置样式
                cellStyle.setAlignment(HorizontalAlignment.CENTER);//横向居中
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//纵向居中
                //生成一个字体
                XSSFFont font = workbook.createFont();
                font.setFontHeightInPoints((short) 16);
                font.setFontName("黑体");
                cellStyle.setFont(font);
                cell.setCellStyle(cellStyle);
            }*/
            if (row == null)
                continue;
            for (int j = 0; j < row.getPhysicalNumberOfCells(); j++)
            {//列
                XSSFCell cell = row.getCell(j);
                cell.setCellType(CellType.STRING);
                if (cell == null)
                    continue;
                String key = cell.getStringCellValue();
                if (map.containsKey(key)){
                    if(key!="head"){
                        cell.setCellValue(map.get(key));
                    }
                }
            }
        }
    }

    /**
     * 生成excel
     * @param map
     * @return
     * @throws Exception
     */
    public static String  createExcle(Map<String, String> map) throws Exception{
    	String excleOutUrl=ReadPropertiesUtil.readUrl("excelOutUrl")+map.get("excleName")+".xlsx";
        XSSFWorkbook workBook = createWorkBook(map);
        OutputStream stream = new FileOutputStream(excleOutUrl);
        workBook.write(stream);
        workBook.close();
        String url=ReadPropertiesUtil.readUrl("nginxUrl")+"excelOutUrl"+"/"+map.get("excleName")+".xlsx";
        return url;
    }
	

}
